create external table jms_dm.dm_door_sign_rate_sum_dt
(
date_time date COMMENT '日期' ,
manage_name string COMMENT '管理大区' ,
manage_code string COMMENT '管理大区code' ,
provider_desc string COMMENT '省份' ,
provider_id string COMMENT '省份id' ,
agent_name string COMMENT '代理区' ,
agent_code string COMMENT '代理区code' ,
scan_site_code string COMMENT '扫描网点' ,
scan_site_name string COMMENT '扫描网点code' ,
is_timely_count bigint COMMENT '时效签收票数' ,
is_door_count  bigint COMMENT '送货上门票数' ,
is_xusign_count   bigint COMMENT '虚假签收' ,
is_stage_sign_count  bigint COMMENT '送入驿站' ,
is_performance_flag bigint COMMENT '标电联票数' ,
is_stagesignanddoor_count bigint COMMENT '送入驿站快件中打标电联票数',
signed_type string COMMENT '类型' ,
signed_type_count bigint COMMENT '类型汇总' ,
code   string COMMENT '类型code',
type string COMMENT '1,网点汇总，2类型汇总'

 )
comment '送货上门率汇总表'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_door_sign_rate_sum_dt'
TBLPROPERTIES (     'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');




create  table jms_dm.dm_door_sign_rate_sum_dt
(
date_time date COMMENT '日期' ,
scan_site_code varchar(100) COMMENT '扫描网点' ,
agent_code varchar(100) COMMENT '代理区code' ,
manage_name varchar(100) COMMENT '管理大区' ,
manage_code varchar(100) COMMENT '管理大区code' ,
provider_desc varchar(100) COMMENT '省份' ,
provider_id varchar(100) COMMENT '省份id' ,
agent_name varchar(100) COMMENT '代理区' ,
scan_site_name varchar(100) COMMENT '扫描网点code' ,
is_timely_count bigint COMMENT '时效签收票数' ,
is_door_count  bigint COMMENT '送货上门票数' ,
is_xusign_count   bigint COMMENT '虚假签收' ,
is_stage_sign_count  bigint COMMENT '送入驿站' ,
is_performance_flag bigint COMMENT '标电联票数' ,
is_stagesignanddoor_count bigint COMMENT '送入驿站快件中打标电联票数',
signed_type varchar(300) COMMENT '类型' ,
signed_type_count bigint COMMENT '类型汇总' ,
code   varchar(100) COMMENT '类型code',
type string COMMENT '1,网点汇总，2类型汇总'
  )
ENGINE=OLAP
DUPLICATE KEY(date_time,scan_site_code,agent_code)
comment '送货上门率汇总表'
PARTITION BY RANGE (date_time) (
   START ('2022-11-01') END ('2022-12-09') EVERY (INTERVAL 1 day))
   DISTRIBUTED BY HASH(date_time,scan_site_code,agent_code) BUCKETS 4
PROPERTIES (
'replication_num' = '1',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-180',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '4',
'in_memory' = 'false',
'storage_format' = 'V2'
)


--2023.3.20新增字段
alter table jms_dm.dm_door_sign_rate_sum_dt add columns( sign_big string comment '签收大类' ) cascade


alter table jms_dm.dm_door_sign_rate_sum_dt add column ( sign_big varchar(500) comment '签收大类'   )  ;


--2023.3.20新增字段
alter table bidefault.dm_door_sign_rate_sum_dt add columns( should_num int comment '应上门票数'
                                                         ,should_door_num int comment '打标上门票数'
) cascade


alter table jms_dm.dm_door_sign_rate_sum_dt add column ( should_num int comment '应上门票数'
                                                        ,should_door_num int  comment '打标上门票数' )  ;



--2023.3.20新增字段
alter table bidefault.dm_door_sign_rate_sum_dt add columns( no_door_yz_num int comment '未上门驿站票数'
                                                           ,no_door_jk_num int comment '未上门柜机票数'
) cascade


alter table jms_dm.dm_door_sign_rate_sum_dt add column ( no_door_yz_num int comment '未上门驿站票数'
                                                        ,no_door_jk_num int  comment '未上门柜机票数' )  ;


alter table jms_dm.dm_door_sign_rate_sum_dt    add columns( is_dk int comment '是否DK仓库,1是0否') cascade